package dao;

import commonUtil.CommonConst;
import connection.JdbcConnection;
import dataValueObject.CustomerInfoValue;

import java.sql.*;
import java.util.ArrayList;

import static connection.JdbcConnection.getConn;

/**
 * Created by xiezy3 on 2016/8/9.
 */
public class CustomerInfoDAO {
    //通过查询id的最大值来获取新的id值便于插入数据后获取主键，同时数据库取消自增长主键
    public static int getNewId(String colName,String tableName){
        JdbcConnection jdbcConn = new JdbcConnection();
        Connection conn = getConn();
        int id = -1;
        String sql = "select max("+colName+") from "+tableName;
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                id = rs.getInt(1);
            }
            id++;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }

    public static int insertCustInfo(CustomerInfoValue customerInfo) {
        JdbcConnection jdbcConn = new JdbcConnection();
        Connection conn = getConn();
        int isSuccess = 0;
        String sql = "insert into customer_info (customer_id,customer_name,customer_add,job,state) values(?,?,?,?,?)";
        PreparedStatement pstmt;
        int newId = getNewId("customer_id","customer_info");//获取主键
        if (newId > 0) {
            try {
                pstmt = (PreparedStatement) conn.prepareStatement(sql);
                pstmt.setInt(1, newId);
                pstmt.setString(2, customerInfo.getCustomerName());
                pstmt.setString(3, customerInfo.getCustomerAdd());
                pstmt.setString(4, customerInfo.getJob());
                pstmt.setString(5, customerInfo.getState());

                isSuccess = pstmt.executeUpdate();
                jdbcConn.close(null, conn, pstmt);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (isSuccess > 0) {
            return newId;
        }else {
            return isSuccess;
        }
    }

    public static int deleteCust(CustomerInfoValue customerInfo) {
        JdbcConnection jdbcConn = new JdbcConnection();
        Connection conn = getConn();
        int isSuccess = 0;
        String sql = "update customer_info set state='" + CommonConst.State.IN_VALIDATE + "' where customer_id='" + customerInfo.getCustomerId() + "'";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            isSuccess = pstmt.executeUpdate();
            jdbcConn.close(null,conn,pstmt);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isSuccess;
    }

    public static CustomerInfoValue[] queryCustInfoByName(String custName){
        JdbcConnection jdbcConn = new JdbcConnection();
        ArrayList<CustomerInfoValue> custInfoList = new ArrayList<CustomerInfoValue>();
        Connection conn = getConn();
        PreparedStatement pstmt;
        String sql = "select * from customer_info where customer_name = '"+custName+"'";
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                if(CommonConst.State.VALIDATE.equals(rs.getString("state"))){
                    CustomerInfoValue custInfo = new CustomerInfoValue();
                    custInfo.setCustomerId(rs.getInt("customer_id"));
                    custInfo.setCustomerName(rs.getString("customer_name"));
                    custInfo.setCustomerAdd(rs.getString("customer_add"));
                    custInfo.setJob(rs.getString("job"));
                    custInfo.setState(CommonConst.State.VALIDATE);
                    custInfoList.add(custInfo);
                }
            }
            jdbcConn.close(rs,conn,pstmt);
        }catch (SQLException e) {
            e.printStackTrace();
        }

        return custInfoList.toArray(new CustomerInfoValue[0]);
    }
    public static CustomerInfoValue queryCustInfoById(int custId){
        JdbcConnection jdbcConn = new JdbcConnection();
        Connection conn = getConn();
        PreparedStatement pstmt;
        CustomerInfoValue custInfo = new CustomerInfoValue();
        String sql = "select * from customer_info where customer_id = '"+custId+"'";
        try {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                //排除state为失效的数据
                if(CommonConst.State.IN_VALIDATE.equals(rs.getString("state"))){
                    continue;
                }
                custInfo.setCustomerId(rs.getInt("customer_id"));
                custInfo.setCustomerName(rs.getString("customer_name"));
                custInfo.setCustomerAdd(rs.getString("customer_add"));
                custInfo.setJob(rs.getString("job"));
                custInfo.setState(CommonConst.State.VALIDATE);

            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return  custInfo;
    }

}
